数据库安全性管理

CREATE USER [IF NOT EXISTS] '用户名'[@'主机地址或标识']
    [IDENTIFIED [WITH auth_plugin] BY '密码' | RANDOM PASSWORD]
    [WITH resource_option ...]
    [password_option];

-- 创建用户 student，密码 student123，只允许从本机登录
CREATE USER IF NOT EXISTS 'student'@'localhost'
    IDENTIFIED BY 'student123';

-- 同时创建两个用户：
-- teacher1 允许从任何主机连过来（% 表示任意IP）
-- teacher2 只允许从指定IP登录，密码由系统随机生成
CREATE USER 'teacher1'@'%' IDENTIFIED BY 'teacher123',
            'teacher2'@'192.168.1.23' IDENTIFIED BY RANDOM PASSWORD;

-- 创建 teacher5，主机不限
-- 限制：每小时最多查询 60 次、更新 100 次
CREATE USER 'teacher5'@'%'
    IDENTIFIED BY 'teacher5pwd'
    WITH MAX_QUERIES_PER_HOUR 60
         MAX_UPDATES_PER_HOUR 100;

-- 直接往 mysql.user 表插入记录创建用户
-- 字段顺序：host, user, 密码哈希, ssl 类型, ssl 密钥, x509 颁发者, x509 主体
INSERT INTO mysql.user
    (host, user, authentication_string,
     ssl_type, ssl_cipher, x509_issuer, x509_subject)
VALUES('localhost', 'teacher6', SHA('t6123'), '', '', '', '');

-- 必须刷新权限缓存，否则新用户无法登录
FLUSH PRIVILEGES;

-- 查询 mysql.user 表的用户名和主机信息
SELECT User, Host FROM mysql.user;

-- 查看 teacher1 的用户名、主机、每小时最大查询次数
SELECT User, Host, max_questions
FROM mysql.user
WHERE User = 'teacher1';

-- 这两个函数在调试权限问题时非常有用
SELECT CURRENT_USER(), USER();

-- 把 student@localhost 的密码改成 student123
ALTER USER 'student'@'localhost' IDENTIFIED BY 'student123';

-- 给指定用户改密码
SET PASSWORD FOR 'student'@'localhost' = 'student123';

-- 不写 FOR 子句则修改当前登录用户自己的密码
SET PASSWORD = 'newpwd456';

# 注意：这是 Windows/Linux 命令行命令，不是 SQL 语句
# 执行后会先要求输入旧密码，再要求输入新密码
mysqladmin -u student -p password

UPDATE mysql.user
SET authentication_string = SHA('student123')
WHERE User = 'student' AND Host = 'localhost';

-- 直接改表必须刷新权限
FLUSH PRIVILEGES;

-- 完整语法：可同时改用户名和主机信息
RENAME USER '原用户名'@'原主机' TO '新用户名'@'新主机';

-- 例：把 teacher1 重命名为 teacher10
-- 同时把允许登录的主机从 % (任意) 改为 localhost (仅本机)
RENAME USER 'teacher1'@'%' TO 'teacher10'@'localhost';

-- 删除单个用户
DROP USER 'student'@'localhost';

-- 一次删除多个用户
DROP USER 'teacher5'@'%', 'teacher6'@'localhost';

-- 用户不存在时不报错
DROP USER IF EXISTS 'student'@'localhost';

GRANT 权限名称 [(字段列表)] [, 权限名称 [(字段列表)]] ...
    ON 授权层次及对象
    TO '用户名'@'主机' [, '用户名'@'主机'] ...
    [WITH GRANT OPTION];

-- 给 teacher10 服务器上的所有权限，并允许它再把权限转授给别人
GRANT ALL
    ON *.*
    TO 'teacher10'@'localhost'
    WITH GRANT OPTION;

-- 给 teacher6 服务器级别的查询、插入、更新权限（不允许转授）
GRANT SELECT, INSERT, UPDATE
    ON *.*
    TO 'teacher6'@'localhost';

-- 给 teacher4 在 teaching 库上的查询、建表、插入权限
-- ON teaching.* 表示 teaching 库下的所有表/视图
GRANT SELECT, CREATE, INSERT
    ON teaching.*
    TO 'teacher4'@'localhost';

-- 给 teacher3 在 teaching.t 表上的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE
    ON teaching.t
    TO 'teacher3'@'localhost';

-- 只允许 teacher2 查询 t 表的 tno 和 tn 两个字段
-- 字段名写在权限名后面的括号里
GRANT SELECT(tno, tn)
    ON teaching.t
    TO 'teacher2'@'192.168.1.23';

-- 查看指定用户的所有权限
SHOW GRANTS FOR 'student'@'localhost';

-- 查看当前登录用户自己的权限
SHOW GRANTS FOR CURRENT_USER;

-- 上面这条还可以简写为
SHOW GRANTS;

REVOKE 权限名称 [(字段列表)] ...
    ON 回收权限的层次及对象
    FROM '用户名'@'主机';

-- 收回 teacher10 在 teaching 库上的 SELECT 和 DELETE 权限
REVOKE SELECT, DELETE
    ON teaching.*
    FROM 'teacher10'@'localhost';

-- 一键收回该用户的所有权限和转授权（用户本身还在）
REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM 'teacher10'@'localhost';

-- 给 teacher3 在 teaching.t 上的 SELECT 和 UPDATE 权限
-- 同时允许 teacher3 把这两个权限再授给别的用户
GRANT SELECT, UPDATE
    ON teaching.t
    TO 'teacher3'@'localhost'
    WITH GRANT OPTION;

-- 语法（角色和用户长得很像，都是 名字@主机 格式）
CREATE ROLE '角色名'[@'主机'] [, '角色名'[@'主机']] ...;

-- 例 1：一次创建两个全局可用的角色
CREATE ROLE 'teacher', 'administrator';

-- 例 2：创建只能在本机生效的角色
CREATE ROLE 'developer'@'localhost';

-- 例 1：管理员角色，授予所有权限
GRANT ALL PRIVILEGES
    ON *.*
    TO 'administrator';

-- 例 2：教师角色，授予 teaching.sc 表的增删改查
GRANT SELECT, INSERT, UPDATE, DELETE
    ON teaching.sc
    TO 'teacher';

-- 语法：GRANT 角色 TO 用户
GRANT '角色名' TO '用户名'@'主机';

-- 把 teacher 角色分配给 teacher2 用户
GRANT 'teacher' TO 'teacher2'@'%';

-- 语法：让指定用户的指定角色在登录时自动激活
SET DEFAULT ROLE '角色名' TO '用户名'@'主机';

-- 例 1：让 teacher2 的 teacher 角色生效
SET DEFAULT ROLE 'teacher' TO 'teacher2'@'%';

-- 例 2：让 teacher3 拥有的所有角色都生效
SET DEFAULT ROLE ALL TO 'teacher3'@'%';

-- 用户登录后查询当前生效的角色
SELECT CURRENT_ROLE();

-- 语法：REVOKE 角色 FROM 用户
-- 注意被收的是「角色」，从「用户」身上拿走
REVOKE '角色名' FROM '用户名'@'主机';

-- 例：把 teacher 角色从 teacher2 身上拿走
REVOKE 'teacher' FROM 'teacher2'@'%';

-- 把整个角色从系统里删掉，所有绑定关系自动解除
DROP ROLE 'teacher';